package com.appspot.thinkhea.model;

import java.io.ByteArrayOutputStream;
import java.util.Vector;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.graphics.Bitmap;
import android.graphics.BitmapFactory;

public class MyDBHelper extends SQLiteOpenHelper {
	private static final String DATABASE_NAME = "PictureCounter.db";
	private static final int DATABASE_VERSION = 1;
	private String tag = "MyDBHelper";
	// Picture
	private static final String TABLE_NAME = "Picture";
	private static final String ID = "id";
	private static final String NAME = "name";
	private static final String DETAIL = "detail";
	private static final String IMAGE = "image";
	private static final String RATE = "rating";
	private static String[] FROM = { ID, NAME, DETAIL, IMAGE, RATE };
	private static String ORDER_BY = ID + " DESC";

	// List Record
	private static final String TABLE_NAME_RECORD = "Record";
	private static final String ID_RECORD = "id";
	private static final String RECORD_DATE = "record_date";
	private static final String P1 = "picture1";
	private static final String P1_C = "picture1_count";
	private static final String P2 = "picture2";
	private static final String P2_C = "picture2_count";
	private static final String P3 = "picture3";
	private static final String P3_C = "picture3_count";
	private static final String P4 = "picture4";
	private static final String P4_C = "picture4_count";
	private static final String P5 = "picture5";
	private static final String P5_C = "picture5_count";
	private static final String P6 = "picture6";
	private static final String P6_C = "picture6_count";
	private static final String P7 = "picture7";
	private static final String P7_C = "picture7_count";
	private static final String P8 = "picture8";
	private static final String P8_C = "picture8_count";
	private static final String P9 = "picture9";
	private static final String P9_C = "picture9_count";
	private static final String P10 = "picture10";
	private static final String P10_C = "picture10_count";
	private static String[] FROM_RECORD = { ID_RECORD, RECORD_DATE, P1, P1_C,
			P2, P2_C, P3, P3_C, P4, P4_C, P5, P5_C, P6, P6_C, P7, P7_C, P8,
			P8_C, P9, P9_C, P10, P10_C };
	private static String ORDER_BY_RECORD = RECORD_DATE + " DESC";

	private SQLiteDatabase db;

	/** Create a helper object for the Events database */
	public MyDBHelper(Context ctx) {
		super(ctx, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		try {
			db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " (" + ID
					+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + NAME
					+ " TEXT NOT NULL," + DETAIL + " TEXT NOT NULL, " + IMAGE
					+ " blob not null, " + RATE + " INTEGER " + "); ");

			db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_RECORD + " ("
					+ ID_RECORD + " INTEGER PRIMARY KEY AUTOINCREMENT, "
					+ RECORD_DATE + " TEXT NOT NULL," + P1 + " INTEGER, "
					+ P1_C + " INTEGER, " + P2 + " INTEGER, " + P2_C
					+ " INTEGER, " + P3 + " INTEGER, " + P3_C + " INTEGER, "
					+ P4 + " INTEGER, " + P4_C + " INTEGER, " + P5
					+ " INTEGER, " + P5_C + " INTEGER, " + P6 + " INTEGER, "
					+ P6_C + " INTEGER, " + P7 + " INTEGER, " + P7_C
					+ " INTEGER, " + P8 + " INTEGER, " + P8_C + " INTEGER, "
					+ P9 + " INTEGER, " + P9_C + " INTEGER, " + P10
					+ " INTEGER, " + P10_C + " INTEGER " + "); ");

		} catch (Exception e) {
			Log.e("MyDBHelper", "Table creation failed. Dropping all tables.");
		}
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME_RECORD);
		onCreate(db);
	}

	public SQLiteDatabase open() {
		db = MyDBHelper.this.getWritableDatabase();
		return db;
	}

	public void close() {
		db.close();
	}

	public long addPicture(Picture p) {
		ContentValues values = new ContentValues();
		values.put(NAME, p.getName());
		values.put(DETAIL, p.getDetail());
		if (p.getImage() != null) {
			ByteArrayOutputStream out = new ByteArrayOutputStream();
			p.getImage().compress(Bitmap.CompressFormat.PNG, 100, out);
			values.put(IMAGE, out.toByteArray());
		} else {
			values.put(IMAGE, "");
		}
		values.put(RATE, p.getRate());
		return db.insertOrThrow(TABLE_NAME, null, values);
	}

	public long updatePicutre(Picture p) {
		long result = -1;
		ContentValues values = new ContentValues();
		int id = p.getId();
		values.put(ID, id);
		values.put(NAME, p.getName());
		values.put(DETAIL, p.getDetail());
		if (p.getImage() != null) {
			ByteArrayOutputStream out = new ByteArrayOutputStream();
			p.getImage().compress(Bitmap.CompressFormat.PNG, 100, out);
			values.put(IMAGE, out.toByteArray());
		} else {
			values.put(IMAGE, "");
		}
		values.put(RATE, p.getRate());
		String[] whereArgs = new String[] { Integer.valueOf(id).toString() };
		try {
			result = db.update(TABLE_NAME, values, ID + "=?", whereArgs);
		} catch (Exception e) {
			Log.e("DB", "updatePicutre()" + e.getMessage());
		}
		return result;
	}

	public long removePicutre(Picture p) {
		long result = -1;
		int id = p.getId();
		String[] whereArgs = new String[] { Integer.valueOf(id).toString() };
		try {
			result = db.delete(TABLE_NAME, ID + "=?", whereArgs);
		} catch (Exception e) {
			Log.e("DB", "removePicutre()" + e.getMessage());
		}
		return result;
	}

	public Picture getPictureByID(int pid) {
		String[] whereArgs = new String[] { Integer.valueOf(pid).toString() };
		Cursor c = db.query(TABLE_NAME, FROM, ID + "=?", whereArgs, null, null,
				ORDER_BY);
		Picture p = null;
		while (c.moveToNext()) {
			int id = c.getInt(0);
			String name = c.getString(1);
			String detail = c.getString(2);
			byte[] blob = c.getBlob(3);
			Bitmap bmp = null;
			if (blob.length > 1) {
				bmp = BitmapFactory.decodeByteArray(blob, 0, blob.length);
			}
			int rate = c.getInt(4);

			p = new Picture(id, name, detail, bmp, rate);
		}
		c.close();
		return p;
	}
	
	public Vector<Picture> getRatedPicture(int pid) {
		String[] whereArgs = new String[] { Integer.valueOf(pid).toString() };
		Cursor c = db.query(TABLE_NAME, FROM, RATE + "=?", whereArgs, null, null,
				ORDER_BY);
		Vector<Picture> v = new Vector<Picture>();
		while (c.moveToNext()) {
			int id = c.getInt(0);
			String name = c.getString(1);
			String detail = c.getString(2);
			byte[] blob = c.getBlob(3);
			Bitmap bmp = null;
			if (blob.length > 1) {
				bmp = BitmapFactory.decodeByteArray(blob, 0, blob.length);
			}
			int rate = c.getInt(4);

			v.add(new Picture(id, name, detail, bmp, rate));
		}
		c.close();
		return v;
	}
	
	public Vector<Picture> getAllPicutres() {
		Cursor c = db.query(TABLE_NAME, FROM, null, null, null, null, ORDER_BY);
		Vector<Picture> v = new Vector<Picture>();
		while (c.moveToNext()) {
			int id = c.getInt(0);
			String name = c.getString(1);
			String detail = c.getString(2);
			byte[] blob = c.getBlob(3);
			Bitmap bmp = null;
			if (blob.length > 1) {
				bmp = BitmapFactory.decodeByteArray(blob, 0, blob.length);
			}
			int rate = c.getInt(4);

			Picture p = new Picture(id, name, detail, bmp, rate);
			v.add(p);
		}
		c.close();
		return v;
	}
	
	public long addRecord(Record r) {
		ContentValues values = new ContentValues();
		values.put(RECORD_DATE, r.getDateString());
		values.put(P1, -1);
		values.put(P2, -1);
		values.put(P3, -1);
		values.put(P4, -1);
		values.put(P5, -1);
		values.put(P6, -1);
		values.put(P7, -1);
		values.put(P8, -1);
		values.put(P9, -1);
		values.put(P10, -1);
		values.put(P1_C, -1);
		values.put(P2_C, -1);
		values.put(P3_C, -1);
		values.put(P4_C, -1);
		values.put(P5_C, -1);
		values.put(P6_C, -1);
		values.put(P7_C, -1);
		values.put(P8_C, -1);
		values.put(P9_C, -1);
		values.put(P10_C, -1);

		Vector<Player> p = r.getPlayerList();
		for (int a = 0; a < p.size(); a++) {
			int pid = p.get(a).getId();
			int pval = p.get(a).getValue();
			switch (a) {
			case 0:
				values.put(P1, pid);
				values.put(P1_C, pval);
				break;
			case 1:
				values.put(P2, pid);
				values.put(P2_C, pval);
				break;
			case 2:
				values.put(P3, pid);
				values.put(P3_C, pval);
				break;
			case 3:
				values.put(P4, pid);
				values.put(P4_C, pval);
				break;
			case 4:
				values.put(P5, pid);
				values.put(P5_C, pval);
				break;
			case 5:
				values.put(P6, pid);
				values.put(P6_C, pval);
				break;
			case 6:
				values.put(P7, pid);
				values.put(P7_C, pval);
				break;
			case 7:
				values.put(P8, pid);
				values.put(P8_C, pval);
				break;
			case 8:
				values.put(P9, pid);
				values.put(P9_C, pval);
				break;
			case 9:
				values.put(P10, pid);
				values.put(P10_C, pval);
				break;
			}
		}
		return db.insertOrThrow(TABLE_NAME_RECORD, null, values);
	}

	public long updateRecord(Record r) {
		long result = -1;
		ContentValues values = new ContentValues();
		int id = r.getId();
		values.put(ID_RECORD, id);
		values.put(RECORD_DATE, r.getDateString());
		values.put(P1, -1);
		values.put(P2, -1);
		values.put(P3, -1);
		values.put(P4, -1);
		values.put(P5, -1);
		values.put(P6, -1);
		values.put(P7, -1);
		values.put(P8, -1);
		values.put(P9, -1);
		values.put(P10, -1);
		values.put(P1_C, -1);
		values.put(P2_C, -1);
		values.put(P3_C, -1);
		values.put(P4_C, -1);
		values.put(P5_C, -1);
		values.put(P6_C, -1);
		values.put(P7_C, -1);
		values.put(P8_C, -1);
		values.put(P9_C, -1);
		values.put(P10_C, -1);

		Vector<Player> p = r.getPlayerList();
		for (int a = 0; a < p.size(); a++) {
			int pid = p.get(a).getId();
			Log.i(tag, "pid:" + pid);
			int pval = p.get(a).getValue();
			switch (a) {
			case 0:
				values.put(P1, pid);
				values.put(P1_C, pval);
				break;
			case 1:
				values.put(P2, pid);
				values.put(P2_C, pval);
				break;
			case 2:
				values.put(P3, pid);
				values.put(P3_C, pval);
				break;
			case 3:
				values.put(P4, pid);
				values.put(P4_C, pval);
				break;
			case 4:
				values.put(P5, pid);
				values.put(P5_C, pval);
				break;
			case 5:
				values.put(P6, pid);
				values.put(P6_C, pval);
				break;
			case 6:
				values.put(P7, pid);
				values.put(P7_C, pval);
				break;
			case 7:
				values.put(P8, pid);
				values.put(P8_C, pval);
				break;
			case 8:
				values.put(P9, pid);
				values.put(P9_C, pval);
				break;
			case 9:
				values.put(P10, pid);
				values.put(P10_C, pval);
				break;
			}
		}

		String[] whereArgs = new String[] { Integer.valueOf(id).toString() };
		try {
			result = db.update(TABLE_NAME_RECORD, values, ID + "=?", whereArgs);
		} catch (Exception e) {
			Log.e("DB", "updateRecord()" + e.getMessage());
		}
		return result;
	}

	public long removeRecord(Record r) {
		long result = -1;
		int id = r.getId();
		String[] whereArgs = new String[] { Integer.valueOf(id).toString() };
		try {
			result = db.delete(TABLE_NAME_RECORD, ID + "=?", whereArgs);
		} catch (Exception e) {
			Log.e("DB", "removeRecord()" + e.getMessage());
		}
		return result;
	}
	
	public long removeRecordByID(int id){
		long result = -1;
		String[] whereArgs = new String[] { Integer.valueOf(id).toString() };
		try {
			result = db.delete(TABLE_NAME_RECORD, ID + "=?", whereArgs);
		} catch (Exception e) {
			Log.e("DB", "removeRecord()" + e.getMessage());
		}
		return result;
	}

	public Record getRecordByID(int id) {
		String[] whereArgs = new String[] { Integer.valueOf(id).toString() };
		Cursor c = db.query(TABLE_NAME_RECORD, FROM_RECORD, ID + "=?",
				whereArgs, null, null, ORDER_BY_RECORD);
		Record r = null;
		if (c.moveToFirst()) {
			r = new Record();
			r.setId(c.getInt(0));
			r.setRecord_Date(c.getString(1));

			Vector<Player> list = new Vector<Player>();
			int p1id = c.getInt(2);
			if (p1id > 0) {
				Player p = new Player();
				p.setId(p1id);
				p.setValue(c.getInt(3));
				list.add(p);
			}
			int p2id = c.getInt(4);
			if (p2id > 0) {
				Player p = new Player();
				p.setId(p2id);
				p.setValue(c.getInt(5));
				list.add(p);
			}
			int p3id = c.getInt(6);
			if (p3id > 0) {
				Player p = new Player();
				p.setId(p3id);
				p.setValue(c.getInt(7));
				list.add(p);
			}
			int p4id = c.getInt(8);
			if (p4id > 0) {
				Player p = new Player();
				p.setId(p4id);
				p.setValue(c.getInt(9));
				list.add(p);
			}
			int p5id = c.getInt(10);
			if (p5id > 0) {
				Player p = new Player();
				p.setId(p5id);
				p.setValue(c.getInt(11));
				list.add(p);
			}
			int p6id = c.getInt(12);
			if (p6id > 0) {
				Player p = new Player();
				p.setId(p6id);
				p.setValue(c.getInt(13));
				list.add(p);
			}
			int p7id = c.getInt(14);
			if (p7id > 0) {
				Player p = new Player();
				p.setId(p7id);
				p.setValue(c.getInt(15));
				list.add(p);
			}
			int p8id = c.getInt(16);
			if (p8id > 0) {
				Player p = new Player();
				p.setId(p8id);
				p.setValue(c.getInt(17));
				list.add(p);
			}
			int p9id = c.getInt(18);
			if (p9id > 0) {
				Player p = new Player();
				p.setId(p9id);
				p.setValue(c.getInt(19));
				list.add(p);
			}
			int p10id = c.getInt(20);
			if (p10id > 0) {
				Player p = new Player();
				p.setId(p10id);
				p.setValue(c.getInt(21));
				list.add(p);
			}
			r.setPlayerList(list);
		}
		c.close();
		return r;
	}

	public Vector<Record> getAllRecords() {
		Cursor c = db.query(TABLE_NAME_RECORD, FROM_RECORD, null, null, null,
				null, ORDER_BY_RECORD);
		Vector<Record> v = new Vector<Record>();
		while (c.moveToNext()) {
			Record r = new Record();
			r.setId(c.getInt(0));
			r.setRecord_Date(c.getString(1));

			Vector<Player> list = new Vector<Player>();
			int p1id = c.getInt(2);
			if (p1id > 0) {
				Player p = new Player();
				p.setId(p1id);
				p.setValue(c.getInt(3));
				list.add(p);
			}
			int p2id = c.getInt(4);
			if (p2id > 0) {
				Player p = new Player();
				p.setId(p2id);
				p.setValue(c.getInt(5));
			}
			int p3id = c.getInt(6);
			if (p3id > 0) {
				Player p = new Player();
				p.setId(p3id);
				p.setValue(c.getInt(7));
			}
			int p4id = c.getInt(8);
			if (p4id > 0) {
				Player p = new Player();
				p.setId(p4id);
				p.setValue(c.getInt(9));
			}
			int p5id = c.getInt(10);
			if (p5id > 0) {
				Player p = new Player();
				p.setId(p5id);
				p.setValue(c.getInt(11));
			}
			int p6id = c.getInt(12);
			if (p6id > 0) {
				Player p = new Player();
				p.setId(p6id);
				p.setValue(c.getInt(13));
			}
			int p7id = c.getInt(14);
			if (p7id > 0) {
				Player p = new Player();
				p.setId(p7id);
				p.setValue(c.getInt(15));
			}
			int p8id = c.getInt(16);
			if (p8id > 0) {
				Player p = new Player();
				p.setId(p8id);
				p.setValue(c.getInt(17));
			}
			int p9id = c.getInt(18);
			if (p9id > 0) {
				Player p = new Player();
				p.setId(p9id);
				p.setValue(c.getInt(19));
			}
			int p10id = c.getInt(20);
			if (p10id > 0) {
				Player p = new Player();
				p.setId(p10id);
				p.setValue(c.getInt(21));
			}
			r.setPlayerList(list);
			v.add(r);
		}
		c.close();
		return v;
	}
}